package com.asen.commons.core.jdbc.util;

import com.asen.commons.core.jdbc.Jdbc;
import com.asen.commons.core.jdbc.param.ColumnInfo;
import com.asen.commons.core.meta.entity.EntityObject;
import com.asen.commons.core.meta.entity.FieldObject;
import com.asen.commons.core.meta.loader.EntityObjectLoader;
import com.asen.commons.utils.JsonMapper;
import com.asen.commons.utils.SpringUtil;
import com.asen.commons.utils.StringUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * SQL工具类
 *
 * @author Gary
 * @version 1.0
 * @date 2020-01-18
 */
public class DbUtils {

    public static int execSql(String sql) throws Exception {
        Jdbc jdbc = SpringUtil.getBean(Jdbc.class);
        return jdbc.update(sql);
    }

    public static List<Map<String, Object>> query(String sql) throws Exception {
        Jdbc jdbc = SpringUtil.getBean(Jdbc.class);
        return jdbc.query(sql);
    }

    public static <T> List<T> query(String sql, Class<T> clazz) throws Exception {
        return query(sql, clazz, false);
    }

    public static <T> List<T> query(String sql, Class<T> clazz, boolean autoConvert) throws Exception {
        List<T> result = null;

        List<Map<String, Object>> list = query(sql);
        if (list != null && list.size() > 0) {
            if (autoConvert) {
                EntityObject eo = EntityObjectLoader.getInstance().load(clazz);
                List<FieldObject> fieldList = eo.getFieldList();
                Map<String, FieldObject> fieldMap = fieldList.stream().collect(Collectors.toMap(FieldObject::getColumn, Function.identity(), (x, y) -> y));
                list.forEach(map -> {
                    fieldMap.forEach((column, fieldObject) -> {
                        Object value = map.get(column);
                        if (value != null) {
                            map.put(fieldObject.getName(), value);
                            map.remove(column);
                        }
                    });
                });
            }
            result = JsonMapper.json2list(JsonMapper.obj2json(list), clazz);
        }

        return result;
    }

    public static int count(String sql) throws Exception {
        return count(sql, false);
    }

    public static int count(String sql, boolean autoCount) throws Exception {
        Jdbc jdbc = SpringUtil.getBean(Jdbc.class);

        if (autoCount) {
            StringBuilder countSql = new StringBuilder();
            countSql.append("SELECT COUNT(*) FROM (");
            countSql.append(sql);
            countSql.append(") _t");
            sql = countSql.toString();
        }

        SqlRowSet rowSet = jdbc.queryForRowSet(sql);

        int count = 0;
        if (rowSet.next()) {
            count = rowSet.getInt(1);
        }

        return count;
    }

    public static ColumnInfo getColumnInfo(String tableName, String columnName) throws Exception {
        DataSource dataSource = SpringUtil.getBeanQuietly(DataSource.class);
        if (dataSource != null) {
            String database = dataSource.getConnection().getCatalog();
            StringBuilder sql = new StringBuilder("SELECT * FROM `information_schema`.`COLUMNS`")
                    .append(" WHERE LOWER(`TABLE_SCHEMA`)='")
                    .append(StringUtils.lowerCase(database))
                    .append("' AND LOWER(`TABLE_NAME`)='")
                    .append(StringUtils.lowerCase(tableName))
                    .append("' AND LOWER(`COLUMN_NAME`)='")
                    .append(StringUtils.lowerCase(columnName))
                    .append("'");
            List<ColumnInfo> list = query(sql.toString(), ColumnInfo.class, true);
            if (list.size() > 0) {
                return list.get(0);
            }
        }
        return null;
    }

    /**
     * 判断字段是否字符类型
     *
     * @param dataType
     * @return
     */
    public static boolean isCharacterType(String dataType) {
        return StringUtils.containsAny(dataType, "char", "varchar", "text", "tinytext", "mediumtext", "longtext");
    }
}
